package com.sqbang.dbcompare.biz;

import com.sqbang.dbcompare.constant.CommonConst;
import com.sqbang.dbcompare.constant.Whether;
import com.sqbang.dbcompare.constant.enums.FieldTypeMappingEnum;
import com.sqbang.dbcompare.pojo.bo.*;
import com.sqbang.dbcompare.pojo.cache.CommonData;
import com.sqbang.dbcompare.pojo.dto.DatabaseInfoDto;
import com.sqbang.dbcompare.util.FieldUtil;
import com.sqbang.dbcompare.util.Tools;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 数据库 业务工具类
 * @author suqiongbang
 * @date 2021/10/22 19:38
 */
@Component
public class DatabaseBiz {

    /**
     * 获取所有表结构
     *
     * @param key   数据库连接信息key
     * @throws Exception exception
     */
    public LinkedHashMap<String, TableBo> getTableStruct(Integer key){
        Connection conn = Tools.getConnection(key);
        try {
            ResultSet rs = this.commonQuery(conn, CommonConst.MySql.QRY_SHOW_TABLES);
            // 获取数据库下所有表名
            List<String> tableNameList = this.obtainTableNameList(rs);
            // 获取表的注释
            DatabaseInfoDto databaseInfoDto = CommonData.databaseInfoList.get(key.intValue());
            ResultSet rsComment = this.commonQuery(conn, String.format(CommonConst.MySql.QRY_SHOW_TABLE_COMMENT, databaseInfoDto.getDatabase()) );
            LinkedHashMap<String, String> tableCommentMap = this.obtainTableComment(rsComment);

            // 遍历数据库下所有表的结构
            LinkedHashMap<String, TableBo> tableNameAndTableBodyMap = new LinkedHashMap<>();
            for (String tableName : tableNameList) {
                ResultSet rs2 = this.commonQuery(conn, CommonConst.MySql.QRY_DESCRIBE + tableName);
                LinkedHashMap<String, FieldBo> fieldLinkedHashMap = this.obtainTableBody(rs2);

                ResultSet rs3 = this.commonQuery(conn, CommonConst.MySql.QRY_SHOW_INDEX + tableName);
                LinkedHashMap<String, IndexBo> indexLinkedHashMap = this.obtainTableIndex(rs3);

                // 构造表模型实体
                TableBo body = new TableBo();
                body.setTableName(tableName);
                body.setFieldMap(fieldLinkedHashMap);
                body.setIndexMap(indexLinkedHashMap);
                body.setComment(tableCommentMap.get(tableName));
                tableNameAndTableBodyMap.put(tableName, body);
            }
            return tableNameAndTableBodyMap;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取表大概信息
     */
    public List<TableInfoBo> getTableInfo(Integer databaseKey, List<TableBriefBo> tableNameList, String ignoreTablePrefix) {
        List<TableInfoBo> resultList = new ArrayList<>(tableNameList.size());
        Connection conn = Tools.getConnection(databaseKey);
        try {
            for (TableBriefBo tableBo : tableNameList) {
                // 获取表字段
                ResultSet rs2 = this.commonQuery(conn, CommonConst.MySql.QRY_DESCRIBE + tableBo.getTableName());
                LinkedHashMap<String, FieldBo> fieldLinkedHashMap = this.obtainTableBody(rs2);

                TableInfoBo tableInfoBo = new TableInfoBo();
                tableInfoBo.setTableName(tableBo.getTableName());
                tableInfoBo.setTableCamelName(Tools.underline2PropertyName(tableBo.getTableName(), Whether.NO, ignoreTablePrefix));
                tableInfoBo.setTableUpperCaseName(Tools.underline2PropertyName(tableBo.getTableName(), Whether.YES, ignoreTablePrefix));
                tableInfoBo.setTableComment(tableBo.getComment());
                List<TableInfoBo.FieldInfoBo> fieldInfoBoList = new ArrayList<>();
                fieldLinkedHashMap.forEach((fieldName, fieldBo) -> {
                    TableInfoBo.FieldInfoBo fieldInfoBo = new TableInfoBo.FieldInfoBo();
                    fieldInfoBo.setFieldName(fieldName);
                    fieldInfoBo.setFieldCamelName(Tools.underline2PropertyName(fieldName, Whether.NO, ""));
                    fieldInfoBo.setIsPrimaryKey(fieldBo.getIsPrimaryKey());
                    FieldTypeMappingEnum typeMappingEnum = FieldTypeMappingEnum.of(fieldBo.getFieldTypeEnum().toString());
                    fieldInfoBo.setFieldValueType(typeMappingEnum.getMapping());
                    fieldInfoBo.setFieldValuePackage(typeMappingEnum.getPackagePath());
                    fieldInfoBo.setFieldComment(fieldBo.getComment());
                    fieldInfoBoList.add(fieldInfoBo);
                });
                tableInfoBo.setFieldList(fieldInfoBoList);
                resultList.add(tableInfoBo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }

    /**
     * 获取数据库下所有表名
     * @param databaseKey 数据库连接信息的序号
     * @return
     */
    public List<String> listTable(Integer databaseKey) {
        Connection conn = Tools.getConnection(databaseKey);
        try {
            ResultSet rs = this.commonQuery(conn, CommonConst.MySql.QRY_SHOW_TABLES);
            return this.obtainTableNameList(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public List<TableBriefBo> listTableComment(Integer databaseKey) {
        Connection conn = Tools.getConnection(databaseKey);
        try {
            // 获取表的注释
            DatabaseInfoDto databaseInfoDto = CommonData.databaseInfoList.get(databaseKey.intValue());
            ResultSet rsComment = this.commonQuery(conn, String.format(CommonConst.MySql.QRY_SHOW_TABLE_COMMENT, databaseInfoDto.getDatabase()) );
            LinkedHashMap<String, String> tableCommentMap = this.obtainTableComment(rsComment);

            List<TableBriefBo> resultList = new ArrayList<>();
            tableCommentMap.forEach((tableName, comment) -> {
                TableBriefBo tableBriefBo = new TableBriefBo();
                tableBriefBo.setTableName(tableName);
                tableBriefBo.setComment(comment);
                resultList.add(tableBriefBo);
            });
            return resultList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * 通用查询方法，返回结果集
     *
     * @param conn
     * @param sql
     * @return ResultSet
     * @throws SQLException
     */
    public ResultSet commonQuery(Connection conn, String sql) throws SQLException {
        Statement stmt = Objects.requireNonNull(conn).createStatement();
        return stmt.executeQuery(sql);
    }

    /**
     * 获取数据库表名列表的私有处理方法
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<String> obtainTableNameList(ResultSet rs) throws SQLException {
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            // columnIndex最大为1
            list.add(rs.getString(1));
        }
        return list;
    }

    /**
     * 根据结果集获取单表内所有字段模型
     * @param rs
     * @return
     * @throws SQLException
     */
    public LinkedHashMap<String, FieldBo> obtainTableBody(ResultSet rs) throws SQLException {
        LinkedHashMap<String, FieldBo> fieldMap = new LinkedHashMap<>();
        while (rs.next()) {
            FieldBo field = new FieldBo();
            // 此处 rs 的取值范围 1~9 数据库里面执行对应的sql 可知只有9列
            String rsField = rs.getString(1);
            String rsType = rs.getString(2);
            String rsNull = rs.getString(4);
            String rsKey = rs.getString(5);
            String rsDefault = rs.getString(6);
            String rsExtra = rs.getString(7);
            String rsComment = rs.getString(9);
            // 转化值
            field.setName(rsField);
            if (!StringUtils.isEmpty(rsType)) {
                FieldUtil.handleTableField4Type(field, rsType.toLowerCase(Locale.ROOT));
            }
            field.setDefaultValue(rsDefault);
            field.setComment(rsComment);
            field.setIsPrimaryKey("PRI".equals(rsKey) ? Whether.YES : Whether.NO);
            FieldUtil.handleTableField4Null(field, rsNull);
            FieldUtil.handleTableField4AutoIncr(field, rsExtra);

            fieldMap.put(rsField, field);
        }

        return fieldMap;
    }

    /**
     * 根据结果集获取单表的注释
     * @param rs
     * @return
     * @throws SQLException
     */
    private LinkedHashMap<String, String> obtainTableComment(ResultSet rs) throws SQLException {
        LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
        while (rs.next()) {
            // 此处 rs 取值范围 1~2 数据库里面执行对应的sql 可知只有2列
            String rsTableName = rs.getString(1);
            String rsTableComment = rs.getString(2);
            if (rsTableComment == null) {
                rsTableComment = "";
            }
            fieldMap.put(rsTableName, rsTableComment);
        }
        return fieldMap;
    }

    private List<IndexBo> obtainTableIndexList(ResultSet rs) throws SQLException {
        List<IndexBo> indexList = new ArrayList<>();
        while (rs.next()) {
            IndexBo indexBo = new IndexBo();
            // 此处 rs 取值范围 1~15 数据库里面执行“show index from  xx” 可知只有15列
            indexBo.setTable(rs.getString(1));
            indexBo.setNonUnique(Integer.valueOf(rs.getString(2)));
            indexBo.setKeyName(rs.getString(3));
            indexBo.setSeqInIndex(rs.getInt(4));
            indexBo.setColumnName(rs.getString(5));
            indexBo.setSubPart(rs.getString(8) == null ? null : Integer.valueOf(rs.getString(8)));
            indexBo.setIndexType(rs.getString(11));
            String str13 = rs.getString(13);
            indexBo.setIndexComment(str13 == null ? "" : str13);

            indexList.add(indexBo);
        }
        return indexList;
    }

    /**
     * 根据结果集获取单表内所有索引
     * @param rs
     * @return
     * @throws SQLException
     */
    private LinkedHashMap<String, IndexBo> obtainTableIndex(ResultSet rs) throws SQLException {
        List<IndexBo> indexBoList = this.obtainTableIndexList(rs);
        LinkedHashMap<String, IndexBo> indexMap = new LinkedHashMap<>();
        if (!CollectionUtils.isEmpty(indexBoList)) {
            // 根据索引名称分组（将组合索引合并在一起）
            Map<String, List<IndexBo>> indexBoMap = indexBoList.stream().collect(Collectors.groupingBy(IndexBo::getKeyName));
            indexBoMap.forEach((keyName, indexList) -> {
                IndexBo indexBo = indexList.get(0);
                indexBo.setColumnName(this.mergeColumnNameForCombinedIndex(indexList));
                indexMap.put(keyName, indexBo);
            });
        }
        return indexMap;
    }

    /**
     * 合并组合索引的名称
     * @return
     */
    private String mergeColumnNameForCombinedIndex(List<IndexBo> indexList) {
        Collections.sort(indexList, Comparator.comparing(IndexBo::getSeqInIndex));
        List<String> nameList = new ArrayList<>();
        for (IndexBo indexBo : indexList) {
            nameList.add("`" + indexBo.getColumnName() + "`" + (indexBo.getSubPart() == null ? "" : "(" + indexBo.getSubPart() + ")"));
        }
        return nameList.stream().collect(Collectors.joining(","));
    }

}
